import pymysql
import json

import allure, pymysql

dbinfo = {
    "host": "dohko.mysql.001.master.hualala.com",
    "user": "root",
    "password": "hualaladev",
    "port": 3306
}

# test_dbinfo = {
#     "host": "dohko.mysql.001.master.hualala.com",
#     "user": "root",
#     "password": "hualaladev",
#     "port": 3306
# }

class DbConnect():
    @allure.step("建立数据库连接")
    def __init__(self, db_cof, database="db_pay"):
        """打开数据库连接"""
        self.db_cof = db_cof
        self.db = pymysql.connect(database=database, cursorclass=pymysql.cursors.DictCursor, **db_cof)
        self.cursor = self.db.cursor()

    @allure.step("查询sql语句，所有数据")
    def select_all(self, sql):
        """sql查询s所有数据"""
        self.cursor.execute(sql)
        results = self.cursor.fetchall()    #查询所有的数据
        return results

    @allure.step("查询sql语句，最后一条数据")
    def select_lastOne(self, sql):
        """sql查询最后一条数据"""
        self.cursor.execute(sql)
        results = self.cursor.fetchone()    #查询最后一条数据
        return results

    @allure.step("执行sql语句")
    def execut(self, sql):
        """执行sql语句（增，删，改）"""
        try:
            """尝试执行"""
            self.cursor.execute(sql)
            self.db.commit()    #提交动作
        except:
            """发生错误时回滚"""
            self.db.rollback()

    @allure.step("关闭数据库连接")
    def close(self):
        self.db.close()


def select_sql_all(sql, db_cof=dbinfo, dbname="db_mis"):# dbname 是数据库名称
    """sql查询_所有数据"""
    db = DbConnect(db_cof, dbname)
    res = db.select_all(sql)
    db.close()
    return res

def select_sql_lastOne(sql, db_cof=dbinfo, dbname="xxx"):
    """sql查询_最后一条数据"""
    db = DbConnect(db_cof, dbname)
    res = db.select_lastOne(sql)
    db.close
    return res

def exectue_sql(sql, db_cof=dbinfo, dbname=""):
    print(dbname)
    """执行sql语句"""
    db = DbConnect(db_cof, dbname)
    db.execut(sql)
    db.close



if __name__ == '__main__':
    sql = "INSERT INTO db_pay.tbl_fsm_settle_unit (settleUnitName, companyID, userType, outUserID, outUserName, groupID, shopCount, settleMode, packageRemainAmount, settleIncomTotal, settleBalance, freezeBalance, lastTransactionID, defaultAccount, settleSpendingTotal, feeRatio, isActiveAutoTransfer, autoTransferDays, timeAmountRship, minTransferAmount, maxTransferDays, autoTransferMinAmount, isRemindAmount, remindAmount, poundageMinAmount, poundageAmount, startDate, lastTransferTime, bankAccount, unionBankNo, bankCode, bankProvinceCode, bankProvinceName, bankCityCode, bankCityName, bankNO, sysSource, bankName, receiverType, receiverName, receiverLinkman, receiverPhone, receiverMobile, receiverEmail, firstSettleDate, lastSettleDate, lastSettleSuccessDate, lastCashSuccessDate, remark, isOutcomeAcount, operatorID, operatorName, operatorMobile, accountCheckStatus, accountCheckFlowNo, accountCheckTriggerTime, accountCheckReturnTime, accountCheckMsg, bankPersonType, processInstanceID, processStatus, signStatus, processFailReason, auditLimitDate, auditRecentLimitDate, reportStatus, reportMsg, merchantNo, transferMerchantNo, transferPrivateKey, cumulativeMoney, cumulativeDate, processReturnTime, processTime, `action`, actionStamp, createStamp, actionTime, createTime, syncBankStatus, syncBankFailMsg, spMerchantNo, openPayStatus, openPayFailMsg, openPayInstanceID, bankRemainPhone, bankCardNoType, bankCardNo, reportVersion, nameLastUpdateTime, nameUpdateCount, signMsg, signOperatorID, signMode, normalStageStatus, normalStageFailMsg, autoNormalMark, processIsSuccess, processSubmitTime, settleParamStatus) VALUES( '北京艺源之味餐厅-2', 0, 31, '', '', 6, 1, 0, 0.0, 46134.84, 826.02, 0.00, 25834068, 0, 45308.82, 0.00, 0, 0, 0, 0.00, 0, 0.00, 0, 0.00, 0.00, 0.00, 20160713, 0, '0200003509000139979', '', 'ICBC', '', '', '', '', '01020000', 10, '工商银行北京望京支行', 2, '北京艺源之味餐厅', '', '', '', '', 19000101, 19000101, 19000101, 19000101, '', 0, 0, '', '', 3, '', 0, 0, '', 0, 0, 3, 1, '', 20170910, 99991231, -1, '交易成功', 'M100007062', '', '', 0, 0, 0, 0, 2, '2019-08-12 11:08:13', NULL, 20170913103653, 20160713183232, 40, '交易成功', '', 10, '', 0, '', 0, '', 0, 0, 0, NULL, NULL, 0, '', '', 0, 0, 0, '1111111111');"
    # sql1 = "DELETE from anti_addiction_template where name='中国网游防沉迷自动化模板名称1595494061'"
    # result = select_sql_all(sql, dbname="db_mis")
    # result1 = (result)
    # print(result1)
    # for k in result1:
    #     j = k
    #     print(k, result1[j])
    # sql1 = "update tbl_mis_apply_process_urge set requestID = '1988' where requestID = '419976'"
    for i in range(2000):
        exectue_sql(sql, dbname="db_pay")


